# coding:utf-8
# -*- coding: utf-8 -*-
# @Time : 2020/9/3 11:58
# @Author: 王鹏-wangpeng99
# @File : main.py
import dash
from dash.dependencies import Input, Output, State
import dash_core_components as dcc
import dash_html_components as html
import plotly.graph_objs as go
from datetime import datetime
from datetime import timedelta
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from utils import dtutils

# 创建engine
engine = create_engine("sqlite:///db/batterydash.db?charset=utf8&check_same_thread=False", echo=True)
# 创建session类型
DBSession = sessionmaker(bind=engine)
# 创建session对象，进行增删改查:
session = DBSession()


# 绘制电压图
def volt_scatter():
    sql = "select ts,volt from battery order by ts"
    cusor = session.execute(sql)
    result = cusor.fetchall()
    sctx = [datetime.strftime(dtutils.str2dt(item[0]) + timedelta(hours=8), '%Y-%m-%d %H:%M:%S') for item in result]
    scty = [float(item[1]) for item in result]
    trace = go.Scatter(
        x=sctx,
        y=scty,
        name='电压',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(255, 0, 0, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    layout = go.Layout(
        title='电压',
        yaxis={
            'hoverformat': ''  # 如果想显示小数点后两位'.2f'，显示百分比'.2%'
        }
    )
    return go.Figure(
        data=[trace],
        layout=layout
    )


# 绘制电流图
def current_scatter():
    sql = "select ts,current from battery order by ts"
    cusor = session.execute(sql)
    result = cusor.fetchall()
    sctx = [datetime.strftime(dtutils.str2dt(item[0]) + timedelta(hours=8), '%Y-%m-%d %H:%M:%S') for item in result]
    scty = [item[1] for item in result]
    trace = go.Scatter(
        x=sctx,
        y=scty,
        name='电流',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(0, 255, 0, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    layout = go.Layout(
        title='电流',
        yaxis={
            'hoverformat': ''  # 如果想显示小数点后两位'.2f'，显示百分比'.2%'
        }
    )
    return go.Figure(
        data=[trace],
        layout=layout
    )


# 绘制电量图
def battery_scatter():
    sql = "select ts,battery from battery order by ts"
    cusor = session.execute(sql)
    result = cusor.fetchall()
    sctx = [datetime.strftime(dtutils.str2dt(item[0]) + timedelta(hours=8), '%Y-%m-%d %H:%M:%S') for item in result]
    scty = [item[1] for item in result]
    trace = go.Scatter(
        x=sctx,
        y=scty,
        name='电量百分比',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(0, 0, 255, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    layout = go.Layout(
        title='电量百分比',
        yaxis={
            'hoverformat': ''  # 如果想显示小数点后两位'.2f'，显示百分比'.2%'
        }
    )
    return go.Figure(
        data=[trace],
        layout=layout
    )

# 绘制wifi图
def wifi_scatter():
    sql = "select ts,link,level,noise from wifi order by ts"
    cusor = session.execute(sql)
    result = cusor.fetchall()
    sctx = [datetime.strftime(dtutils.str2dt(item[0]) + timedelta(hours=8), '%Y-%m-%d %H:%M:%S') for item in result]
    scty = [item[1] for item in result]
    trace_link = go.Scatter(
        x=sctx,
        y=scty,
        name='link',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(0, 0, 255, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    scty = [item[2] for item in result]
    trace_level = go.Scatter(
        x=sctx,
        y=scty,
        name='level',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(255, 0, 0, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    scty = [item[3] for item in result]
    trace_noise = go.Scatter(
        x=sctx,
        y=scty,
        name='noise',
        mode='lines+markers',
        marker=dict(
            size=10,
            color='rgba(0, 255, 0, 0.8)',  # set color equal to a variable
            colorscale='Viridis',  # one of plotly colorscales
            # showscale=True
        )
    )
    layout = go.Layout(
        title='wifi情况',
        yaxis={
            'hoverformat': ''  # 如果想显示小数点后两位'.2f'，显示百分比'.2%'
        }
    )
    return go.Figure(
        data=[trace_link,trace_level,trace_noise],
        layout=layout
    )

# 查询结果，并生成表格
def generate_talbe():
    sql = "select ts,volt,current,battery from battery order by ts desc limit 50"
    cusor = session.execute(sql)
    result = cusor.fetchall()
    tabledatalist = [
        (i + 1, datetime.strftime(dtutils.str2dt(result[i][0]) + timedelta(hours=8), '%Y-%m-%d %H:%M:%S'), result[i][1],
         result[i][2], str(result[i][3]) + "%") for i in range(len(result))]
    return html.Div([
        html.P('查询成功，查询时间：{},查询结果如下:'.format(dtutils.nowTimestampStr()), style={'font-size': '20px'}),
        html.Table(
            # Header
            [html.Tr([html.Th(col) for col in ['序号', '时间', '电压', '电流', '电量']],
                     style={'font-size': '20px', 'text-align': 'center'})] +
            # Body
            [html.Tr([
                html.Td(tabledatalist[i][col], style={'font-size': '20px', 'text-align': 'center'}) for col in
                range(len(tabledatalist[0]))
            ]) for i in range(len(tabledatalist))],
            className='gridtable')
    ])


app = dash.Dash('电池电量')
app.layout = html.Div(children=[
    #绘图的div
    html.Div(children=[
            html.Div(children=[
                html.H1(children=['电池电量'], style={
                    'textAlign': 'center',
                    'font-size': '20px'
                }),
                html.Button("刷新", id='refreshBtn', style={
                    'font-size': '20px'
                })
            ]),
            html.Div(
                id='all-graph',
                children=[
                    dcc.Graph(id='volt_scatter', figure=volt_scatter()),
                    dcc.Graph(id='current_scatter', figure=current_scatter()),
                    dcc.Graph(id='battery_scatter', figure=battery_scatter()),
                    dcc.Graph(id='wifi_scatter', figure=wifi_scatter()),
                    generate_talbe()
                ]
            )
        ]),
    #上传数据的div,注意，这个DIV隐藏。
    html.Div(children=[
            html.Div(
                children=[
                    html.Div(["datetime: ", dcc.Input(id='volt-ts-input', value=dtutils.nowdt2str2(), type='text')]),
                    html.Div(["volt: ", dcc.Input(id='volt-input', value='0', type='text')]),
                    html.Div(["current: ", dcc.Input(id='current-input', value='0', type='text')]),
                    #html.Div(["battery: ", dcc.Input(id='battery-input', value='0', type='text')]),
                    html.Button("电压电流上传", id='voltUploadBtn', style={
                        'font-size': '20px'
                    }),
                    html.Div(id='output1',children='output1'),
                ],#style={"display":"none"}
            ),
            html.Div(
                children=[
                    html.Div(["datetime: ", dcc.Input(id='wifi-ts-input', value=dtutils.nowdt2str2(), type='text')]),
                    html.Div(["link: ", dcc.Input(id='link-input', value='0', type='text')]),
                    html.Div(["level: ", dcc.Input(id='level-input', value='0', type='text')]),
                    html.Div(["noise: ", dcc.Input(id='noise-input', value='0', type='text')]),
                    html.Button("wifi上传", id='wifiUploadBtn', style={
                        'font-size': '20px'
                    }),
                    html.Div(id='output2',children='output2'),
                ],#style={"display":"none"}
            )
        ],style={"display":"none"}
    ),
])
# 刷新页面
@app.callback(
    Output(component_id='all-graph', component_property='children'),
    [Input('refreshBtn', 'n_clicks')],
)
def refresh(n_clicks):
    children = [
        dcc.Graph(id='volt_scatter', figure=volt_scatter()),
        dcc.Graph(id='current_scatter', figure=current_scatter()),
        dcc.Graph(id='battery_scatter', figure=battery_scatter()),
        dcc.Graph(id='wifi_scatter', figure=wifi_scatter()),
        generate_talbe()
    ]
    return children


# 电压电流增加数据
@app.callback(
    Output(component_id='output1', component_property='children'),
    [Input('voltUploadBtn', 'n_clicks')],
    [State(component_id='volt-ts-input', component_property='value'),
     State(component_id='volt-input', component_property='value'),
     State(component_id='current-input', component_property='value')],
)
def update_output1_div(n_clicks, dtinputstr, volt, current):
    battery=0
    if dtinputstr and len(dtinputstr) > 0:
        dt = dtutils.str2dt(dtinputstr) + timedelta(hours=-8)
        dtstr = dt.strftime("%Y-%m-%d %H:%M:%S")
    else:
        return "没有传送时间戳"
    if float(volt) > 0.0 and int(current) > 0:
        #默认设置放电截止电压9.0v，充满电压12.4v
        battery=int((float(volt)-9.0)/(12.4-9.0)*100.0)
        rawsql = """
            insert into battery (ts,volt,current,battery) values ("{}",{},{},{})
            """
        sql = rawsql.format(dtstr, volt, current, battery)
        print(sql)
        session.execute(sql)
        session.commit()
    return 'Output volt:{},current:{},battery:{}%'.format(volt, current, battery)

# wifi增加数据
@app.callback(
    Output(component_id='output2', component_property='children'),
    [Input('wifiUploadBtn', 'n_clicks')],
    [State(component_id='wifi-ts-input', component_property='value'),
     State(component_id='link-input', component_property='value'),
     State(component_id='level-input', component_property='value'),
     State(component_id='noise-input', component_property='value')],
)
def update_output2_div(n_clicks, dtinputstr, link, level, noise):
    if dtinputstr and len(dtinputstr) > 0:
        dt = dtutils.str2dt(dtinputstr) + timedelta(hours=-8)
        dtstr = dt.strftime("%Y-%m-%d %H:%M:%S")
    else:
        return "没有传送时间戳"
    if int(link) > 0:
        rawsql = """
            insert into wifi (ts,link,level,noise) values ("{}",{},{},{})
            """
        sql = rawsql.format(dtstr, link, level, noise)
        print(sql)
        session.execute(sql)
        session.commit()
    return 'Output link:{},level:{},noise:{}'.format(link, level, noise)


if __name__ == '__main__':
    app.run_server(host='0.0.0.0')
